package package1;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 
 * @author Alvin
 *
 */
public class TestForReadingTxtToExcel {
	public static void main(String[] args) throws IOException {
		String sourceFilePath = "C:/Users/wangyic/Desktop/1.txt";
		String objectFilePath = "C:/Users/wangyic/Desktop/1.xlsx";
		readTxtToExcel(sourceFilePath, objectFilePath, "HPE");
		readTxtToExcel(sourceFilePath, objectFilePath, "HPI");
	}
	private static void readTxtToExcel(String sourceFilePath, String objectFilePath, String sheetName) throws FileNotFoundException, IOException {
		BufferedReader br = new BufferedReader(new FileReader(sourceFilePath));
		XSSFWorkbook wb = new XSSFWorkbook();
		if(new File(objectFilePath).exists()) wb = new XSSFWorkbook(new FileInputStream(new File(objectFilePath)));
		XSSFSheet sheet = wb.getSheet(sheetName)==null?wb.createSheet(sheetName):wb.getSheet(sheetName);
		Map<Integer,Integer> colWidthMap = new HashMap<Integer,Integer>();
		String buf;	int rowNum = 0;	int cellNum = 0; XSSFRow row; String bmiState; boolean hasHead = false; boolean start = false;
		while((buf = br.readLine())!=null)
		{	
			if(start&&buf.startsWith("end")) break;
			if(buf.startsWith(sheetName))
			{
				start = true;continue;
			}
			if(!start) continue;
			if(buf.equals("")||(hasHead&&buf.startsWith("Quote Number")))continue;
			if(buf.startsWith("Quote Number"))	hasHead = true;
			row = sheet.createRow(rowNum);
			bmiState = buf.split(",")[4];
			if(!buf.startsWith("Quote Number")&&!bmiState.equalsIgnoreCase("Quote Review")&&!bmiState.equalsIgnoreCase("Orderable")) continue;
			for(String content:buf.split(","))
			{
				updateMap(colWidthMap,cellNum,content);
				row.createCell(cellNum++).setCellValue(content);
			}
			rowNum++; cellNum = 0;
		}
		for(int len:colWidthMap.keySet())
			sheet.setColumnWidth(len, colWidthMap.get(len)*36*10);
		StringBuffer sb = new StringBuffer();
		for(int i=0;i<rowNum;i++)
		{
			row = sheet.getRow(i);
			String value = row.getCell(0).getStringCellValue();
			if(value.equalsIgnoreCase("quote number"))continue;
			sb.append(value);
			if(i!=rowNum-1)	sb.append(",");
		}
		row = sheet.createRow(rowNum);
		String url = "https://ext-webserv-corona.houston.hp.com/QuoteIds/1.8/quoteServices/searchQuote/html/quotes?queryParams=qtnNr:"+sb.toString();
		row.createCell(0).setCellValue(url);
		FileOutputStream fos = new FileOutputStream(objectFilePath);
		wb.write(fos); br.close(); fos.flush();	fos.close();
		System.err.println("end");
	}
	private static void updateMap(Map<Integer, Integer> colWidthMap,
			int cellNum, String content) {
		if(!colWidthMap.containsKey(cellNum)||content.toCharArray().length>colWidthMap.get(cellNum))
			colWidthMap.put(cellNum, content.toCharArray().length);
	}
}
